﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;

/// <summary>
/// Summary description for ConnectionProcess
/// </summary>
public class ConnectionProcess
{
	public ConnectionProcess()
	{
		//
		// TODO: Add constructor logic here
		//
	}
   public SqlConnection con = new SqlConnection();
    SqlCommand cmd = new SqlCommand();
    void Open_con()
    {
        if (con.State == ConnectionState.Closed)
        {
            con.ConnectionString = @"Data Source=.\sqlexpress;Initial Catalog=TuDienOnLine;Integrated Security=True";
            //con.ConnectionString = "server=.\\sqlexpress;database=TuDienOnLine; integrated security=true;User Instance=True";
           // con.ConnectionString = @"server=.\SQLEXPRESS;AttachDbFilename=|datadirectory|\TTAnhNgu.mdf;Integrated Security=True;User Instance=True";
            con.Open(); 
        }
    }  
    void Close_con()
    {
        con.Close();
    }
    public DataTable Get_data(string sql, params SqlParameter[] pars)
    {
        DataTable dt = new DataTable();
        cmd.Connection = con;
        cmd.CommandText = sql;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Clear();
        cmd.Parameters.AddRange(pars);
        Open_con();
        
        SqlDataReader dr = cmd.ExecuteReader();
        dt.Load(dr);
        dr.Close();

        Close_con();
        return dt;
    }

    public object LayDuLieu(string sql, params SqlParameter[] pars)
    {
        object obj;
        cmd.Connection = con;
        cmd.CommandText = sql;
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Clear();
        cmd.Parameters.AddRange(pars);

        Open_con();
        obj = cmd.ExecuteScalar();
        Close_con();
        return obj;
    }
    public void thuchienlenh(string sql, params SqlParameter[] pars)
    {
        cmd.Connection = con;
        cmd.CommandText = sql;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Clear();
        cmd.Parameters.AddRange(pars);
        Open_con();
        cmd.ExecuteNonQuery();
        Close_con();
    }
    public string CauLenhStore(string sql, params SqlParameter[] pars)
    {
        cmd.Connection = con;
        cmd.CommandText = sql;
        cmd.CommandType = CommandType.StoredProcedure;
        Open_con();
        cmd.Parameters.Clear();
        cmd.Parameters.AddRange(pars);

        cmd.ExecuteNonQuery();
        string kq = cmd.Parameters["@khoa"].Value.ToString();
        Close_con();
        return kq;
    }
}
